using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using XProject.Entity;
namespace XProject.DAO
{

    class PhoneDAO:DBAccess
    {
        #region SQL command
        string SQL_SELECT_ID = "select max(PhoneID) from Phone";
        string SQL_SELECT_BY_CONTACT="SELECT Phone.PhoneID, Phone.PhoneNum, Phone.PhoneType, PhoneType.TypeName, Phone.Note"
+" FROM  PhoneType INNER JOIN Phone ON PhoneType.TypeID = Phone.PhoneType"
+" WHERE (((Phone.ContactID)=@contactID))";
        string SQL_INSERT = "INSERT INTO Phone ( ContactID, PhoneNum, PhoneType, [Note] ) VALUES (@contactID, @phone, @phoneType, @note)";
        string SQL_UPDATE = "UPDATE Phone SET Phone.PhoneNum = @phoneNume, Phone.PhoneType = @phoneType, Phone.[Note] = @note WHERE (((Phone.PhoneID)=@phoneID))";
        string SQL_DELETE = "Delete from Phone where PhoneID=@phoneID";

        #endregion
        #region fields
        string message;
        #endregion
        #region properties
        public string Message
        {
            get { return message; }
        }
        #endregion
        #region methods
        public IList<Phone> SelectByContact(int contactID)
        {
            IList<Phone> list = new List<Phone>();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_BY_CONTACT, conn);
                
                command.Parameters.AddWithValue("@contactID", OleDbType.Integer).Value = contactID;
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        Phone phone = new Phone();
                        phone.PhoneID = dr.GetInt32(0);
                        phone.PhoneNumber = dr.GetString(1);
                        phone.PhoneType.TypeID = dr.GetByte(2);
                        phone.PhoneType.TypeName = dr.GetString(3);
                        phone.Note = dr.GetString(4);
                        list.Add(phone);
                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return list;
        }
        public int Insert(Phone phone, int contactID)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_INSERT, conn);
                command.Parameters.Add("@contact", OleDbType.Integer).Value = contactID;
                command.Parameters.Add("@phoneNum", OleDbType.VarChar).Value=phone.PhoneNumber;
                command.Parameters.Add("@phoneType", OleDbType.TinyInt).Value=phone.PhoneType.TypeID;
                command.Parameters.Add("@note", OleDbType.VarChar).Value=phone.Note;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();
                    command.CommandText=SQL_SELECT_ID;
                    phone.PhoneID = int.Parse(command.ExecuteScalar().ToString());
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return effectedRows;
        }
        public int Update(Phone phone)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_UPDATE, conn);
                command.Parameters.AddWithValue("@phoneNum", OleDbType.VarChar).Value=phone.PhoneNumber;
                command.Parameters.AddWithValue("@phoneType", OleDbType.Integer).Value=phone.PhoneType.TypeID;
                command.Parameters.AddWithValue("@note", OleDbType.VarChar).Value=phone.Note;
                command.Parameters.AddWithValue("@phoneID",OleDbType.Integer).Value=phone.PhoneID;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();                   
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return effectedRows;
        }
        public int Delete(Phone phone)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_DELETE, conn);
                command.Parameters.AddWithValue("@phoneID", OleDbType.Integer).Value = phone.PhoneID;
                
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();                    
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return effectedRows;
        }
            
        #endregion
    }
}
